package com.glela.ningchongqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

public class JDBC测试 {
	static String jdbcUrl = "jdbc:mysql://172.16.100.162:3306/yg_user?useUnicode=true&characterEncoding=UTF-8";
	static String className = "com.mysql.jdbc.Driver";
	static String user = "app";
	static String password = "1qaz-pl,app";
	
	static Connection connection = null;
	
	public static void main(String[] args) {
		getConnection();
		select();
	}

	public static Connection getConnection() {
		try {
			if (connection == null) {
				// 加载数据库驱动
				Class.forName(className);
				connection = DriverManager.getConnection(jdbcUrl, user, password);
				System.out.println("连接成功");
			}
			// 获取数据库连接
		} catch (Exception e) {
			System.out.println("连接失败");
		}
		return connection;
	}

	public static void closeConnection(Connection connection) {
		try {
			connection.close();
			System.out.println("关闭成功");
		} catch (SQLException e) {
			System.out.println("关闭失败");
		}
	}

	public static void select() {
		// 带参数的 SQL 语句, 要设置的值用 ? 占位
		String sql = "SELECT order_sn FROM test20180309.test limit 100";
		try {
			// 传递 SQL 语句
			PreparedStatement statement = connection.prepareStatement(sql);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				String orderSn = resultSet.getString("order_sn");
				String openStoreUserId = selectOpenStoreUserId(orderSn);
				String superiorUserId = selectSuperiorUserId(openStoreUserId);
				String userName = "";
				String nickName = "";
				while (!"".equals(superiorUserId)) {
					Map<String, String> map = selectSuperiorUserInfo(superiorUserId);
					userName = map.get("userName").toString();
					nickName = map.get("nickName").toString();
					superiorUserId = selectSuperiorUserId(superiorUserId);
				}
				System.out.println(orderSn+"-----"+userName+"-------"+nickName);
			}
			resultSet.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(connection);
		}
	}

	public static Map<String, String> selectSuperiorUserInfo(String superiorUserId) {
		Map<String, String> map = new HashMap<String, String>();
		// 带参数的 SQL 语句, 要设置的值用 ? 占位
		String sql = "SELECT cu.user_name user_name,cu.nick_name nick_name FROM yg_user.c_user cu WHERE cu.id = "
				+ superiorUserId;
		try {
			// 传递 SQL 语句
			PreparedStatement statement = connection.prepareStatement(sql);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				map.put("userName", resultSet.getString("user_name"));
				map.put("nickName", resultSet.getString("nick_name"));
			}
			resultSet.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}

	public static String selectOpenStoreUserId(String orderSn) {
		String openStoreUserId = "";
		// 带参数的 SQL 语句, 要设置的值用 ? 占位
		String sql = "SELECT oo.open_store_user_id open_store_user_id FROM yg_order.order_info oi,yg_order.order_object oo WHERE oi.order_object_id = oo.id AND oi.order_sn = "
				+ orderSn;
		try {
			// 传递 SQL 语句
			PreparedStatement statement = connection.prepareStatement(sql);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				openStoreUserId = resultSet.getString("open_store_user_id");
			}
			resultSet.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return openStoreUserId;
	}

	public static String selectSuperiorUserId(String openstoreuserid) {
		String superiorUserId = "";
		// 带参数的 SQL 语句, 要设置的值用 ? 占位
		String sql = "SELECT IFNULL(cui.user_id,'') user_id FROM yg_user.c_user cu, yg_user.c_user_info cui WHERE cu.talent_teacher = cui.persion_id AND cu.id = "
				+ openstoreuserid;
		try {
			// 传递 SQL 语句
			PreparedStatement statement = connection.prepareStatement(sql);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				superiorUserId = resultSet.getString("user_id");
			}
			resultSet.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return superiorUserId;
	}

	public void insert() {
		String sql = "insert into user (UserName,PassWord,UserAge,UserSex) values('丙','123456', 20, 0)";
		Connection connection = getConnection();
		try {
			// 获取数据库操作类
			Statement statement = connection.createStatement();
			// 执行 SQL 语句并返回结果
			int result = statement.executeUpdate(sql);
			if (result != 0) {
				System.out.println("操作成功,受影响" + result + "行");
			}
			statement.close();
		} catch (SQLException e) {
			System.out.println("操作失败");
		} finally {
			closeConnection(connection);
		}
	}

	public void delete() {
		String sql = "delete from user where UserId in (2,3,4)";
		Connection connection = getConnection();
		try {
			Statement statement = connection.createStatement();
			int result = statement.executeUpdate(sql);
			if (result != 0) {
				System.out.println("操作成功,受影响" + result + "行");
			}
			statement.close();
		} catch (SQLException e) {
			System.out.println("操作失败");
		} finally {
			closeConnection(connection);
		}
	}

	public void update() {
		String sql = "update user set UserName = '乙' where UserId = 5";
		Connection connection = getConnection();
		try {
			Statement statement = connection.createStatement();
			int result = statement.executeUpdate(sql);
			if (result != 0) {
				System.out.println("操作成功,受影响" + result + "行");
			}
			statement.close();
		} catch (SQLException e) {
			System.out.println("操作失败");
		} finally {
			closeConnection(connection);
		}
	}

}
